// app/service/sales_order_service.js
'use strict';

const Service = require('egg').Service;
const fs = require('fs').promises;
const path = require('path');
const csv = require('csv-parser');

class SalesOrderService extends Service {
  // 获取订单列表
  async getOrderList(page, pageSize, params) {
    const { ctx } = this;
    // 同时支持 limit 和 pageSize 参数
    const effectivePageSize = pageSize || 20;
    const { platformOrderId, customerId, orderStatus, invoiceStatus } = params;
    
    const query = {
      where: {},
      include: [{
        model: ctx.model.CustomerModel,
        as: 'customer',
        attributes: ['id', 'name']
      }]
    };
    
    if (platformOrderId) {
      query.where.platformOrderId = platformOrderId;
    }
    
    if (customerId) {
      query.where.customerId = customerId;
    }
    
    if (orderStatus) {
      query.where.orderStatus = orderStatus;
    }
    
    if (invoiceStatus) {
      query.where.invoiceStatus = invoiceStatus;
    }
    
    const result = await ctx.model.SalesOrderModel.findAndCountAll({
      ...query,
      limit: parseInt(effectivePageSize),
      offset: (parseInt(page) - 1) * parseInt(effectivePageSize),
      order: [['order_date', 'DESC']]
    });
    
    return {
      list: result.rows,
      total: result.count
    };
  }
  
  // 根据ID获取订单
  async getOrderById(id) {
    const { ctx } = this;
    return await ctx.model.SalesOrderModel.findByPk(id, {
      include: [{
        model: ctx.model.CustomerModel,
        as: 'customer'
      }]
    });
  }
  
  // 创建订单
  async createOrder(orderData) {
    const { ctx } = this;
    return await ctx.model.SalesOrderModel.create(orderData);
  }
  
  // 更新订单
  async updateOrder(id, orderData) {
    const { ctx } = this;
    const order = await ctx.model.SalesOrderModel.findByPk(id);
    
    if (!order) {
      throw new Error('订单不存在');
    }
    
    return await order.update(orderData);
  }
  
  // 删除订单
  async deleteOrder(id) {
    const { ctx } = this;
    const order = await ctx.model.SalesOrderModel.findByPk(id);
    
    if (!order) {
      throw new Error('订单不存在');
    }
    
    return await order.destroy();
  }
  
  // 导入销售订单
  async importOrders(file, fieldMapping) {
    const { ctx } = this;
    
    try {
      // 记录开始时间
      const startTime = Date.now();
      
      // 生成临时文件路径
      const tempDir = path.join(__dirname, '../../temp');
      await fs.mkdir(tempDir, { recursive: true });
      const tempFilePath = path.join(tempDir, `${Date.now()}_${file.filename}`);
      
      // 保存文件
      await fs.copyFile(file.filepath, tempFilePath);
      
      let jsonData = [];
      
      // 根据文件扩展名判断文件类型并解析
      const fileExtension = path.extname(file.filename).toLowerCase();
      
      if (fileExtension === '.csv') {
        // 解析CSV文件
        jsonData = await new Promise((resolve, reject) => {
          const results = [];
          fs.createReadStream(tempFilePath, { encoding: 'utf8' })
            .pipe(csv({ 
              skipEmptyLines: true,
              trim: true
            }))
            .on('data', (data) => {
              // 清理数据
              const cleanedData = {};
              for (const [key, value] of Object.entries(data)) {
                const cleanKey = key ? key.trim() : key;
                const cleanValue = value ? value.toString().trim() : '';
                cleanedData[cleanKey] = cleanValue !== 'undefined' ? cleanValue : '';
              }
              results.push(cleanedData);
            })
            .on('end', () => {
              resolve(results);
            })
            .on('error', reject);
        });
      } else if (fileExtension === '.xlsx' || fileExtension === '.xls') {
        // 解析Excel文件
        try {
          const XLSX = require('xlsx');
          const workbook = XLSX.readFile(tempFilePath);
          const firstSheetName = workbook.SheetNames[0];
          const worksheet = workbook.Sheets[firstSheetName];
          jsonData = XLSX.utils.sheet_to_json(worksheet, { defval: '' });
        } catch (excelError) {
          throw new Error('解析Excel文件失败: ' + excelError.message);
        }
      } else {
        throw new Error('不支持的文件格式，仅支持CSV、XLSX和XLS格式');
      }
      
      // 添加调试日志
      console.log('Parsed JSON data:', jsonData);
      console.log('JSON data length:', jsonData.length);
      
      // 验证并处理数据
      const validOrders = [];
      const errors = [];
      
      // 分批处理大量数据
      const batchSize = 100;
      let processedCount = 0;
      
      for (let i = 0; i < jsonData.length; i++) {
        const row = jsonData[i];
        const rowIndex = i + 2; // +2 because header is row 1, and Excel is 1-indexed
      
        try {
          // 添加调试日志
          console.log('Processing row:', i, 'Data:', row);
          
          // 根据字段映射关系获取订单数据
          let orderData = {
            platformOrderId: '',
            customerId: null,
            orderAmount: 0,
            orderDate: new Date(),
            invoiceStatus: 'uninvoiced',
            orderStatus: 'completed',
            collectionStatus: 'pending'
          };
        
          // 根据字段映射关系更新数据
          if (fieldMapping && Object.keys(fieldMapping).length > 0) {
            for (const [sourceColumn, targetField] of Object.entries(fieldMapping)) {
              // 确保row存在且sourceColumn存在
              if (!row) continue;
              const value = row[sourceColumn] || '';
              if (value === 'undefined') continue;
              
              switch (targetField) {
                case 'orderNumber':
                  orderData.platformOrderId = value.trim();
                  break;
                case 'customerName':
                  // 需要根据客户名称查找客户ID
                  if (value.trim()) {
                    const customer = await ctx.model.CustomerModel.findOne({
                      where: { name: value.trim() }
                    });
                    if (customer) {
                      orderData.customerId = customer.id;
                    }
                  }
                  break;
                case 'orderAmount':
                  orderData.orderAmount = parseFloat(value) || 0;
                  break;
                case 'orderDate':
                  // 尝试解析日期
                  const dateValue = value.trim();
                  if (dateValue) {
                    // 支持多种日期格式
                    const dateFormats = [
                      'YYYY-MM-DD',
                      'YYYY/MM/DD',
                      'DD/MM/YYYY',
                      'MM/DD/YYYY'
                    ];
                    
                    // 简单的日期解析逻辑
                    let parsedDate = new Date(dateValue);
                    if (isNaN(parsedDate.getTime())) {
                      // 如果标准解析失败，尝试其他格式
                      const parts = dateValue.split(/[-/]/);
                      if (parts.length === 3) {
                        // 假设是 YYYY-MM-DD 格式
                        parsedDate = new Date(parts[0], parts[1] - 1, parts[2]);
                      }
                    }
                    
                    if (!isNaN(parsedDate.getTime())) {
                      orderData.orderDate = parsedDate;
                    }
                  }
                  break;
                default:
                  // 忽略未映射的字段
                  break;
              }
            }
          }
          
          // 验证必填字段
          if (!orderData.platformOrderId) {
            errors.push(`第${rowIndex}行: 订单编号不能为空`);
            continue;
          }
          
          // 检查订单编号是否已存在
          const existingOrder = await ctx.model.SalesOrderModel.findOne({
            where: { platformOrderId: orderData.platformOrderId }
          });
          
          if (existingOrder) {
            errors.push(`第${rowIndex}行: 订单编号 ${orderData.platformOrderId} 已存在`);
            continue;
          }
          
          // 添加组织ID
          orderData.organizationId = ctx.state.organizationId || 1;
          
          validOrders.push(orderData);
          processedCount++;
          
          // 分批处理
          if (validOrders.length >= batchSize) {
            await ctx.model.SalesOrderModel.bulkCreate(validOrders);
            console.log(`已处理 ${processedCount} 条订单数据`);
            validOrders.length = 0; // 清空数组
          }
        } catch (rowError) {
          errors.push(`第${rowIndex}行处理失败: ${rowError.message}`);
        }
      }
      
      // 处理剩余的数据
      if (validOrders.length > 0) {
        await ctx.model.SalesOrderModel.bulkCreate(validOrders);
        processedCount += validOrders.length;
      }
      
      // 删除临时文件
      await fs.unlink(tempFilePath);
      
      // 记录结束时间
      const endTime = Date.now();
      const duration = endTime - startTime;
      
      return {
        success: true,
        processedCount,
        errors,
        duration
      };
    } catch (error) {
      // 删除临时文件（如果存在）
      try {
        const tempFilePath = path.join(__dirname, '../../temp', `${Date.now()}_${file.filename}`);
        await fs.unlink(tempFilePath);
      } catch (unlinkError) {
        // 忽略删除临时文件的错误
      }
      
      throw error;
    }
  }
  
  // 获取待匹配的订单列表（用于对账）
  async getPendingOrdersForReconciliation(page = 1, pageSize = 100) {
    const { ctx } = this;
    
    // 构建查询条件：已完成但未匹配的订单
    const query = {
      where: {
        orderStatus: 'completed',
        id: {
          [ctx.app.Sequelize.Op.notIn]: ctx.app.Sequelize.literal(
            '(SELECT sales_order_id FROM reconciliation_results WHERE match_status = "confirmed")'
          )
        }
      },
      include: [{
        model: ctx.model.CustomerModel,
        as: 'customer',
        attributes: ['id', 'name']
      }]
    };
    
    const result = await ctx.model.SalesOrderModel.findAndCountAll({
      ...query,
      limit: parseInt(pageSize),
      offset: (parseInt(page) - 1) * parseInt(pageSize),
      order: [['order_date', 'DESC']]
    });
    
    return {
      list: result.rows,
      total: result.count
    };
  }
}

module.exports = SalesOrderService;